﻿using System.Data;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace StudyCompanion.Commons.Helper;

public class ExcelHelper
{
    /// <summary>
    ///     将excel文件读取成datatable
    /// </summary>
    /// <param name="fileName">文件名称</param>
    /// <param name="sheetName">sheet名称</param>
    /// <param name="isFirstRowColumn">第一行是否为标题</param>
    /// <returns></returns>
    public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
    {
        var data = new DataTable();
        try
        {
            IWorkbook workbook = null;
            workbook = WorkbookFactory.Create(fileName);

            //var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            //if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
            //    workbook = new XSSFWorkbook(fs);
            //else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
            //    workbook = new HSSFWorkbook(fs);

            ISheet sheet = null;
            if (sheetName != null)
                sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
            else
                sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                var firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                var startRow = 0;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            var cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                var column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }

                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                    {
                        var column = new DataColumn(i.ToString());
                        data.Columns.Add(column);
                    }

                    startRow = sheet.FirstRowNum;
                }

                //最后一列的标号
                var rowCount = sheet.LastRowNum;
                for (var i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    if (row == null) continue; //没有数据的行默认是null　　　　　　　

                    var dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                        if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            dataRow[j] = row.GetCell(j).ToString();
                    data.Rows.Add(dataRow);
                }
            }

            return data;
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception: " + ex.Message);
            return null;
        }
    }

    /// <summary>
    ///     将文件流文件读取成datatable
    /// </summary>
    /// <param name="fileName">文件名称</param>
    /// <param name="sheetName">sheet名称</param>
    /// <param name="isFirstRowColumn">第一行是否为标题</param>
    /// <returns></returns>
    public static DataTable ExcelToDataTable(Stream fileStream, string sheetName, bool isFirstRowColumn)
    {
        var data = new DataTable();

        ISheet sheet = null;
        //数据开始行(排除标题行)
        var startRow = 0;
        try
        {
            //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
            var workbook = WorkbookFactory.Create(fileStream);
            //如果有指定工作表名称
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                if (sheet == null) sheet = workbook.GetSheetAt(0);
            }
            else
            {
                //如果没有指定的sheetName，则尝试获取第一个sheet
                sheet = workbook.GetSheetAt(0);
            }

            if (sheet != null)
            {
                var firstRow = sheet.GetRow(0);
                //一行最后一个cell的编号 即总的列数
                int cellCount = firstRow.LastCellNum;
                //如果第一行是标题列名
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            var cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                var column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }

                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }

                //最后一列的标号
                var rowCount = sheet.LastRowNum;
                for (var i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null　　　　　　　

                    var dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        //同理，没有数据的单元格都默认是null
                        var cell = row.GetCell(j);
                        if (cell != null)
                        {
                            if (cell.CellType == CellType.Numeric)
                            {
                                //判断是否日期类型
                                if (DateUtil.IsCellDateFormatted(cell))
                                    dataRow[j] = row.GetCell(j).DateCellValue;
                                else
                                    dataRow[j] = row.GetCell(j).ToString().Trim();
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString().Trim();
                            }
                        }
                    }

                    data.Rows.Add(dataRow);
                }
            }

            return data;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    public static XSSFWorkbook DataTableToExcel(DataTable dt, string sheetName)
    {
        var _workBook = new XSSFWorkbook();
        var style = _workBook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.Center;
        style.VerticalAlignment = VerticalAlignment.Center;

        var sheet = _workBook.CreateSheet(sheetName.Trim());

        var headerRow = sheet.CreateRow(0);
        for (var i = 0; i < dt.Columns.Count; i++)
        {
            sheet.SetDefaultColumnStyle(i, style);

            var cell = headerRow.CreateCell(i);
            cell.SetCellValue(dt.Columns[i].ToString());
        }

        for (var i = 0; i < dt.Rows.Count; i++)
        {
            var row = sheet.CreateRow(i + 1);
            for (var j = 0; j < dt.Columns.Count; j++)
            {
                var obj = dt.Rows[i][j];
                if (obj != null)
                {
                    var cell = row.CreateCell(j);
                    if (obj is double || obj is float || obj is int || obj is long || obj is decimal)
                        cell.SetCellValue(Convert.ToDouble(obj));
                    else if (obj is bool)
                        cell.SetCellValue((bool)obj);
                    else
                        cell.SetCellValue(obj.ToString());
                }
            }
        }

        return _workBook;
    }
}